﻿

CREATE PROCEDURE [dbo].[balcon_SchimbaCodUnitate]  
@oldCodUnit as smallint,
@newCodUnit as smallint AS
BEGIN

---creaza tabelele corespunzatoare
DECLARE @Comanda varchar(5000)
DECLARE @strCodUnitate varchar(40)



BEGIN TRAN 
PRINT('ArticoleAliniate')
--ArticoleAliniate---------------------------------------

ALTER TABLE dbo.Ordonantari DROP CONSTRAINT FK_Ordonantari_ArticoleAliniate
IF @@ERROR != 0 
	GOTO EROARE
ALTER TABLE dbo.JurnaleSolduriInitiale DROP CONSTRAINT FK_JurnaleSolduriInitiale_Conturi
IF @@ERROR != 0 
	GOTO EROARE
ALTER TABLE dbo.ConturiSpeciale DROP CONSTRAINT FK_ConturiSpeciale_Conturi
IF @@ERROR != 0 
	GOTO EROARE




DELETE FROM [ArticoleAliniate] WHERE [CodUnitate]=@oldCodUnit 
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [ArticoleAliniate] SET [CodUnitate]=@newCodUnit
WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Bilant')
--Bilant---------------------------------------
DELETE FROM [Bilant] WHERE [CodUnitate]=@oldCodUnit 
IF @@ERROR != 0 GOTO EROARE

UPDATE [Bilant] SET CodUnitate=@newCodUnit  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('Sterge rulaje')
DELETE FROM [Rulaje] WHERE [CodUnitate]=@oldCodUnit 
IF @@ERROR != 0 
	GOTO EROARE
	
PRINT('Sterge rulajeDoc')
DELETE FROM [RulajeDoc] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Conturi')
--Conturi---------------------------------------


DELETE FROM [Conturi] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [Conturi] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Conturi Valuta')
--ConturiValuta---------------------------------------
DELETE FROM [ConturiValuta] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [ConturiValuta] SET CodUnitate=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE


PRINT('JurnaleSolduriInitiale')
--JurnaleSolduriInitiale---------------------------------------
DELETE FROM [JurnaleSolduriInitiale] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [JurnaleSolduriInitiale] SET [CodUnitate]=@newCodUnit  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('Jurnale')
DELETE FROM [Jurnale] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [Jurnale] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE


PRINT('Parteneri')
DELETE FROM [Parteneri] WHERE [CodUnitate]=@newCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [Parteneri] SET [CodUnitate]=@newCodUnit  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Ordonantari')

DELETE FROM [Ordonantari]  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 GOTO EROARE

UPDATE [Ordonantari] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('RezultatPatrimonial')
--RezultatPatrimonial---------------------------------------
DELETE FROM [RezultatPatrimonial] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

UPDATE [RezultatPatrimonial] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
PRINT(@Comanda)
IF @@ERROR != 0 
	GOTO EROARE

PRINT('RulajeDoc')
--RulajeDoc---------------------------------------

UPDATE [RulajeDoc] SET 
	  [CodUnitate]=@newCodUnit 
	  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Rulaje')
--Rulaje---------------------------------------

UPDATE [Rulaje] SET [CodUnitate]=@newCodUnit
	  WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('VenituriSiCheltuieli')
--VenituriSiCheltuieli---------------------------------------
DELETE FROM [VenituriSiCheltuieli] WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE	

UPDATE [VenituriSiCheltuieli] SET [CodUnitate]=@newCodUnit WHERE [CodUnitate]=@oldCodUnit
IF @@ERROR != 0 
	GOTO EROARE	

ALTER TABLE dbo.Ordonantari ADD CONSTRAINT
	FK_Ordonantari_ArticoleAliniate FOREIGN KEY (ArtAl,CodUnitate) REFERENCES dbo.ArticoleAliniate (ArtAl,CodUnitate)
	ON UPDATE  NO ACTION 
	ON DELETE  NO ACTION 
IF @@ERROR != 0 
	GOTO EROARE	

ALTER TABLE dbo.JurnaleSolduriInitiale ADD CONSTRAINT
	FK_JurnaleSolduriInitiale_Conturi FOREIGN KEY
	(Cont,CodUnitate) REFERENCES dbo.Conturi (Cont,CodUnitate) 
	ON UPDATE  NO ACTION 
	ON DELETE  NO ACTION 
IF @@ERROR != 0 
	GOTO EROARE	
	
ALTER TABLE dbo.ConturiSpeciale ADD CONSTRAINT
	FK_ConturiSpeciale_Conturi FOREIGN KEY
	(Cont,CodUnitate) REFERENCES dbo.Conturi(Cont,CodUnitate) ON UPDATE  NO ACTION 
	ON DELETE  NO ACTION 
IF @@ERROR != 0 
	GOTO EROARE	


COMMIT




GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la schimbare codului unitatii!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:






END